package com.esun.common.utils;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.reflect.MethodSignature;


import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.util.*;

/**
 * SQL语句工具类
 *
 * @author John.xiao
 * @date 2021/3/18 9:13
 */
public class SqlUtils {


	public final static String REGEX = "'|and|exec|execute|insert|select|delete|update|count|drop|\\*|%|chr|mid|master|truncate|" +
			"char|declare|sitename|net user|xp_cmdshell|;|or|-|\\+|,|like'|and|exec|execute|insert|create|drop|" +
			"table|from|grant|use|group_concat|column_name|" +
			"information_schema.columns|table_schema|union|where|select|delete|update|order|by|count|\\*|" +
			"chr|mid|master|truncate|char|declare|or|;|-|--|\\+|,|like|//|/|%|#";

	/**
	 * 把SQL关键字替换为空字符串
	 *
	 * @param param
	 * @return
	 */
	public static String filter(String param) {
		if (param == null) {
			return null;
		}
		// (?i)不区分大小写替换
		return param.replaceAll("(?i)" + REGEX, "");
	}

	/**
	 * 将批量查询参数转成带（）String
	 *
	 * @param list 参数列表
	 * @return 字符串
	 */
	public static String getBatchString(List<String> list) {
		if (list == null || list.size() == 0) {
			return null;
		}
		StringBuilder builder = new StringBuilder();
		for (String s : list) {
			builder.append("'");
			//sql注入过滤
			s = SqlUtils.filter(s);
			builder.append(s);
			builder.append("'");
			builder.append(",");

		}
		builder.setLength(builder.length() - 1);
		return builder.toString();
	}

	/**
	 * 获取aop中的SQL语句
	 * @param point
	 * @param sqlSessionFactory
	 * @return
	 * @throws IllegalAccessException
	 */
	public static String getMybatisSql(ProceedingJoinPoint point, SqlSessionFactory sqlSessionFactory) throws IllegalAccessException {
		Map<String,Object> map = new HashMap<>();
		//1.获取namespace+methodName
		MethodSignature signature = (MethodSignature) point.getSignature();
		Method method = signature.getMethod();
		String namespace = method.getDeclaringClass().getName();
		String methodName = method.getName();
		//2.根据namespace+methdoName获取相对应的MappedStatement
		Configuration configuration = sqlSessionFactory.getConfiguration();
		MappedStatement mappedStatement = configuration.getMappedStatement(namespace+"."+methodName);
//        //3.获取方法参数列表名
//        Parameter[] parameters = method.getParameters();
		//4.形参和实参的映射
		Object[] objects = point.getArgs(); //获取实参
		Annotation[][] parameterAnnotations = method.getParameterAnnotations();
		for (int i = 0;i<parameterAnnotations.length;i++){
			Object object = objects[i];
			if (parameterAnnotations[i].length == 0){ //说明该参数没有注解，此时该参数可能是实体类，也可能是Map，也可能只是单参数
				if (object.getClass().getClassLoader() == null && object instanceof Map){
					map.putAll((Map<? extends String, ?>) object);
					System.out.println("该对象为Map");
				}else{//形参为自定义实体类
					map.putAll(objectToMap(object));
					System.out.println("该对象为用户自定义的对象");
				}
			}else{//说明该参数有注解，且必须为@Param
				for (Annotation annotation : parameterAnnotations[i]){
					if (annotation instanceof Param){
						map.put(((Param) annotation).value(),object);
					}
				}
			}
		}
		//5.获取boundSql
		BoundSql boundSql = mappedStatement.getBoundSql(map);
		return showSql(configuration,boundSql);
	}

	/**
	 * 解析BoundSql，生成不含占位符的SQL语句
	 * @param configuration
	 * @param boundSql
	 * @return
	 */
	private  static String showSql(Configuration configuration, BoundSql boundSql) {
		Object parameterObject = boundSql.getParameterObject();
		List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
		String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
		if (parameterMappings.size() > 0 && parameterObject != null) {
			TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
			if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
				sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
			} else {
				MetaObject metaObject = configuration.newMetaObject(parameterObject);
				for (ParameterMapping parameterMapping : parameterMappings) {
					String propertyName = parameterMapping.getProperty();
					String[] s =  metaObject.getObjectWrapper().getGetterNames();
					s.toString();
					if (metaObject.hasGetter(propertyName)) {
						Object obj = metaObject.getValue(propertyName);
						sql = sql.replaceFirst("\\?", getParameterValue(obj));
					} else if (boundSql.hasAdditionalParameter(propertyName)) {
						Object obj = boundSql.getAdditionalParameter(propertyName);
						sql = sql.replaceFirst("\\?", getParameterValue(obj));
					}
				}
			}
		}
		return sql;
	}

	/**
	 * 若为字符串或者日期类型，则在参数两边添加''
	 * @param obj
	 * @return
	 */
	private static String getParameterValue(Object obj) {
		String value = null;
		if (obj instanceof String) {
			value = "'" + obj.toString() + "'";
		} else if (obj instanceof Date) {
			DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
			value = "'" + formatter.format(new Date()) + "'";
		} else {
			if (obj != null) {
				value = obj.toString();
			} else {
				value = "";
			}
		}
		return value;
	}

	/**
	 * 获取利用反射获取类里面的值和名称
	 *
	 * @param obj
	 * @return
	 * @throws IllegalAccessException
	 */
	private static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
		Map<String, Object> map = new HashMap<>();
		Class<?> clazz = obj.getClass();
		for (Field field : clazz.getDeclaredFields()) {
			field.setAccessible(true);
			String fieldName = field.getName();
			Object value = field.get(obj);
			map.put(fieldName, value);
		}
		return map;
	}

}
